home
***
CD-ROM
|
disk
|
FTP
|
other
***
search
/
Tech Arsenal 1
/
Tech Arsenal (Arsenal Computer).ISO
/
tek-19
/
lspsql2.zip
/
LISPSQL.DOC
< prev
next >
Wrap
Lisp/Scheme
|
1992-08-25
|
19KB
|
500 lines
****************************************************************************
lispsql.doc
Copyright (C) 1991-1992 by Autodesk, Inc.
Permission to use, copy, modify, and distribute this software
for any purpose and without fee is hereby granted, provided
that the above copyright notice appears in all copies and that
both that copyright notice and this permission notice appear in
all supporting documentation.
THIS SOFTWARE IS PROVIDED "AS IS" WITHOUT EXPRESS OR IMPLIED
WARRANTY. ALL IMPLIED WARRANTIES OF FITNESS FOR ANY PARTICULAR
PURPOSE AND OF MERCHANTABILITY ARE HEREBY DISCLAIMED.
*****************************************************************************
The AutoLISP-SQL interface is a number of functions which can be
classified as either high or low level. With high-level functions, access
to the databases are represented as SQL expressions to be automatically
converted to a standard data structure and transferred to the DBMS driver.
The low level functions are designed to allow the programmer to generate
these required structures manually.
To use the AutoLISP-SQL interface load LISPSQL.EXP with the 'xload'
command:
(xload "lispsql")
The general case is that functions return 1 (for TRUE) or nil.
asi_initdrv - Driver initialization
(asi_initdrv drvname)
This function is used to initialize the connection between the
Application Program and the Driver referenced by <drvname>. The DBMS
handle (or driver handle) is allocated by this function. The driver name
<drvname>, specified is the only argument to this function and is the
logical name of the driver which is referenced in the ACAD.ASE file.
This function returns the unique driver handle number on success.
For example:
(if (setq drv (asi_initdrv "DBASE4"))
(prin1 "Driver loaded")
(prin1 "Can't load driver")
)
asi_termdrv - Driver Termination
(asi_termdrv drive)
This function is used to terminate the communication between an
Application Program and the DBMS Driver specified by its handle. The
Driver, its connection and communication handles will be freed up.
This function returns 1, on success.
asi_termsql - Terminate ASI interface
(asi_termsql)
This function is used to terminate the ASI interface in this
Application Program. This function complements the ASI initialization
function listed above. This function returns 1, on success.
Caution: if this function is used in one LISP-application, any
other LISP-application, been active in the same time, will be destroyed.
Warning: You must execute this function at the end of your application
for all the memory to be released.
Interaction with any given database is segmented into seven logical steps.
These steps allow the programmer to open multiple database handles to
control any DBMS. Therefore allowing access to multiple tables inside
each DBMS. Only one SQL statement may be active for a single handle.
These steps are:
-- Connecting to database.
This is, in effect, the process of logging onto a database.
Most databases require a connection of some sort be made to the
database before processing is allowed to take place.
-- Opening a communication handle.
Each SQL statement in ASI shall be processed via a communication
handle. The first step towards execution is the opening of a
handle to a database. This handle can also be called a 'cursor',
although definitions of 'cursor' and 'handle' differ widely, they
are currently used interchangeably in this document.
-- Compiling the original SQL statement.
This step is basically comprised of defining an ASCII SQL string
which represents the SQL statement to be processed. The step
of compiling is required to compress the SQL statement into a
standard structure. It is at this point that a syntax check is
performed on the request.
-- Execution of the SQL statement.
The execution stage normally follows compilation. In this stage
the input and output buffers are defined (if necessary) and the
compiled statement is executed. The input buffer is essentially
a link between program variables and variables in the SQL
expression. The process of specifying the input buffer is
called "binding". The output buffer is designed for data storage
during the fetching of query results. Therefore the output
buffer can be called a "SELECT buffer". The output buffer can be
defined just before fetching.
-- Fetching.
Fetching normally follows the query execution (cursor statement).
Its purpose is to move row by row along the resulting rows set,
select the current row, and place it into the output buffer. To
trace the current position in the resulting rows, set the
communication handle or cursor. If the cursor is updatable you
can update the current row or delete it.
-- Closing a communication handle.
Terminates the SQL statement processing for a given handle and
frees up the handle.
-- Disconnecting from a database.
Basically the same as logging off of a database.
asi_lon - Log on to a database.
(asi_lon dhandle basename username password)
During the connection the connection handle is allocated and initialized.
In order to connect to the database you will specify the database name,
the user name, and the password as the strings (basename, username,
password). In addition, you will specify the driver handle, and the unique
number (dhandle) defined by the previously executed asi_initdrv function
(i.e. you will define the driver which will execute database access
requests). This function returns the connection handles unique number on
success.
For example:
(if (setq con (asi_lon drvnumb "DBSQL" "FRUMKIN" "CREDO"))
(prin1 "Connected to the database")
(prin1 "Can't connect to the database")
)
asi_lof - Log off of a database.
(asi_lof chandle)
This function is used for database disconnection. Specify the unique
number (chandle) of the connection handle, and the defined connection
handle will be freed. This function returns 1 on success.
asi_ohdl - Open a Handle to a Database.
(asi_ohdl chandle)
The communication handle will be allocated and opened for the database
specified by the number of the connection handle (chandle). This function
returns a unique number for the communication handle, if successful.
For example:
(if (setq com (asi_ohdl con))
(prin1 "Handle opened")
(prin1 "Can't open handle")
)
asi_chdl - Close Handle to a Database.
(asi_chdl handle)
This function is used for closing and freeing the communication handle
specified by its number (handle). It highly recommended that the
communication buffer be closed, because in some cases database changes
may be lost if the handle is not closed. This function returns 1 on
success.
asi_com - Compile a specific SQL statement for Processing.
(asi_com handle statement)
This function compiles the SQL statement which is passed as a character
string in the second argument. The statement is converted to an internal
data structure which is returned in the handle argument. This handle is
represented by a unique number. The Driver is responsible for semantic
and security testing of an SQL statement. In the initial string one can
indicate program variables (using ':') that will be replaced by references
to the host variables during the binding process.
For example:
(if (asi_com com "SELECT * from table WHERE col1 = :value")
(prin1 "OK")
)
here 'com' is a unique number for the communication handle, and ":value"
is a variable that must be replaced by a reference to the host variable.
If a syntax error occurs during compilation of the SQL statement, the
up arrow symbol at the error position will be displayed, and an error
message will be printed. This function returns 1 on success.
asi_bnd
(asi_bnd handle varname varvalue vartype length)
This function is used for binding host variables into an SQL statement.
'varname' is a character string that corresponds to the variable name
that comes after the colon in the SQL expression. 'varvalue' is a
character string that defines a host variable value. The variable
'vartype' is character string which defines the host variable type, and
can be any of the following values.
"asi_hint" - int
"asi_hreal" - double
"asi_hchar" - character string
"asi_hshort" - short
"asi_hlong" - long
"asi_hfloat" - float
'length' is the length of the host variable buffer in bytes. If a NULL
value must be bound, then an empty string "" should be passed as the
'varvalue' argument. This function returns 1 on success.
For example:
(if (and
(asi_cex hdl "CREATE TABLE table (a1 (INT), a2 (CHAR 20))")
(asi_com hdl "INSERT INTO table values (:a, :b)")
(asi_bnd hdl "a" "" "asi_hint" 8) ; NULL value
(asi_bnd hdl "b" "text" "asi_hchar" 20) ; 'text' value
)
(prin1 "OK")
)
asi_exe - Execute an SQL statement.
(asi_exe hdl)
This function executes the compiled SQL statement. The internal data
structure is contained in the handle argument, and is represented by its
unique number. This function returns 1 on success.
For example:
(if (and
(asi_com hdl "CREATE TABLE table (a1 (INT), a2 (CHAR 20))")
(asi_exe hdl)
)
(prin1 "OK")
)
asi_cex - Compile and execute in one step.
(asi_cex hdl statement)
Compilation and execution are combined into one step in asi_cex. This
function is used when input buffers are not used. The SQL statement is
passed as a character string in the second argument. The statement
is converted to an internal data structure which is contained in the
hdl argument and is represented by its unique number. This function
returns 1 on success.
asi_fet - Fetching in a forward direction.
(asi_fet com)
This function is used for fetching the result rows set in the
forward direction. The communication handle number is passed as the
only argument. After execution of the query the current position
in the selection set is just before the first row (-1). The asi_fet
function moves the cursor one row forward. If the last row was the
current row prior to an asi_fet call, the current position will be just
after it and 'nil' will be return.
For example:
(while (asi_fet com) ; cycle by all rows
.....
)
asi_fbk - Fetch in a Backward Direction.
(asi_fbk com)
This function is used for fetching the result rows set in the backward
direction. The communication handle number is passed as the only argument.
If the first row was the current row prior to an asi_fbk call then the
current position will be just before it (-1) and nil will be return.
asi_ftr - Fetching the first row.
(asi_ftr com)
This function makes the first row of the resulting rows set current.
The communication handle number is passed as the only argument.
asi_fbr - Fetch the last row.
(asi_fbr com)
This function makes the last row of the resulting rows set current.
The communication handle number is passed as the only argument.
asi_del - Delete the current row while fetching.
(asi_del com)
This function is used to delete the current row while fetching the results
of a query. The communication handle number is passed as the only
argument. In other words this function fulfills a positioned delete SQL
statement. This function will only be successful if and only if the
cursor is updatable (in terms of SQL). This function returns 1 on success.
asi_upd - Update the Current Row while Fetching.
(asi_upd com colname value)
This function is used to update the current row while fetching the
results of a query (Positioned Update). The communication handle number
is passed as the first argument (com). 'colname' is a name of a column
from the table being updated in the form of character string, 'value' is
the new value which will replace the current value in the column. The
new value is passed in as character string. If an empty string ("") is
passed in the 'value' argument, the current value will be changed to NULL.
This function will succeed if and only if the cursor is updatable
(in terms of SQL). This function returns 1 on success.
asi_cmt - Commit a Transaction.
(asi_cmt com)
This function is used to save changes in the database. It should be
used after all of the transaction operations have been executed. This
operation will only work if the DBMS supports transactions and can handle
the COMMIT statement. The communication handle number is passed in the
'com' argument. This function returns 1, on success.
asi_rbk - Roll back a Transaction.
(asi_rbk com)
This function performs a rollback of all of the changes that have not yet
been committed to the database. This function will only work if the
database is capable of performing a rollback. In most cases, a rollback
is poorly defined by most DBMS's. The communication handle number is
passed in the 'com' argument. This function returns 1, on success.
asi_cvl - Get a column value from the current row.
(asi_cvl com ncol)
This function can be used to get the results of a query. On success,
this function returns the value of the column specified by its number,
'ncol'. The communication handle number is passed as the first argument
'com'. The empty string ("") corresponds to a NULL value. Column
numbers start from 0. If the column number exceeds the maximum number
of columns, the function will return nil.
asi_gettable - Get the results of a query as a linked list.
(asi_gettable com)
This function is used to get the results of a query, and return it in a
linked list form. The communication handle number is passed in the
'com' argument. This function should be executed after asi_exe (opening
a cursor). If this function is called while the position in the
selection set is set before the first row, then only the remaining rows
will be returned in the link list. After running this function, the
current position in the resulting set will be set to just after the last
string. On success, this function returns a list which consists of
rows value sub lists. The length of every sub list is equal to the
columns number.
For example
(nth i (nth j (asi_gettable com)))
returns the value from the j-th row and the i-th column.
asi_cds - Get the Description of Query Results.
(asi_cds com column)
This function is used to obtain the full description of all the columns
that are available in the resulting set. It is useful when executing
the query with the column list undefined. The column list can be obtained
just after compilation or execution of the SQL statement. The
communication handle number is passed in the 'com' argument. The 'ncol'
argument specifies the number of the requested column. The function
will return a list of column specifications for the requested column.
This list consists of the column name, the precision, and the scale.
asi_rowqty - Get the number of rows in a selection set.
(asi_rowqty com)
On success, this function returns the total number of rows in the
resulting selection set as a double value. The communication handle
number is passed in the 'com' argument.
Caution: AutoLISP does not support long integer values.
asi_currow - Get the Current Row Number.
(asi_currow com)
On success, this function returns the current row number of the resulting
selection set as a double. The communication handle number is passed in
the 'com' argument.
Caution: AutoLISP does not support long integer values.
asi_synnerpos - Get SQL Syntax error position
(asi_synnerpos com)
This function returns the approximate position of a syntax error that
occurred in the original SQL statement, as detected by the asi_com
function. The communication handle number is passed in the 'com'
argument.
asi_stm - Get Type of the SQL Statement
(asi_stm com)
This function returns the type of SQL statement associated with a
communication handle. The 'com' argument specifies the handle number of
the SQL statement. The possible return types are as follows:
"ASI_DELETE"
"ASI_CREATE"
"ASI_CURSOR" cursor operation
"ASI_GRANT"
"ASI_REVOKE"
"ASI_INSERT"
"ASI_DROP"
"ASI_UPDATE"
"ASI_ALTER"
asi_opr - Get the Stage of the SQL Statement Processing.
(asi_opr com)
This function returns the stage of the SQL statement associated
with a communication handle. The communication handle is passed in the
'com' argument. The possible return values are:
"ASI_COM"
"ASI_EXE"
"ASI_DEL"
"ASI_FET"
"ASI_FBK"
"ASI_UPD"
"ASI_CMT"
"ASI_RBK"
"ASI_FFR"
"ASI_FLR"
ERROR HANDLING.
ASI functions usually return 1 or nil. If a function returns nil
and there is no error message, the cursor contains an error code which
reflects the type of error that occurred. It is up to the application
program to decide what should be done in each case and how to handle
the situation.
asi_errmsg - Get an Error Message.
(asi_errmsg com)
This function returns the error message associated with a communication
handle as a character string. The communication handle is specified by
its number in the 'com' argument.